﻿using AchieveCommon;
using AchieveDALFactory;
using AchieveEntity;
using AchieveInterfaceDAL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Collections;

namespace AchieveBLL
{
    /// <summary>
    /// 生产任务的业务逻辑层
    /// </summary>
    public class ProjectBLL
    {
        IProduceDAL dal = DALFactory.GetProduceDAL();
        public DataTable GetAllProduceData(string strwhere)
        {
            return dal.GetAllProduceData(strwhere);
        }
        public enum WorkType { 
            rush=68,//加急；
            b=1002,
            c=55,
            d=1000,
            e=1003,
            f=1004,
            g=57
        }


        /// <summary>
        /// 变更生产任务单类型，生产任务单变更为加急，FWORKTYPEID
        /// </summary>
        /// <param name="workTypeID">目标订单类型id</param>
        /// <returns></returns>
        public static bool alterWorkType(string FBillNo, int workTypeID)
        {
        // update ICMO set FWORKTYPEID = 68 where FBillNo='YF2018001_118'
           string sql = @"update ICMO set FWORKTYPEID = @workTypeID where FBillNo=@FBillNo";
           SqlParameter[] paras = { new SqlParameter("@FBillNo", SqlDbType.NVarChar, 20),
                                  new SqlParameter("@workTypeID", SqlDbType.Int, 20),
                                  };
           paras[0].Value = FBillNo;
           paras[1].Value = workTypeID;
            try
            {
                return (SqlHelper.ExecuteNonQuery(SqlHelper.connStrK3,CommandType.Text,sql,paras)>0);
            }
            catch (Exception)
            {
                
                throw;
            }
         
         }
        /// <summary>
        /// 变更生产任务单类型(批量)，生产任务单变更为加急，FWORKTYPEID
        /// </summary>
        /// <param name="FBillNos">订单号，用逗号，隔开</param>
        /// <returns>bool</returns>
        public static bool alterWorkTypeBatch(string FBillNos, int workTypeID)
        { 
            string[] fBillNo = FBillNos.Trim(',').Split(',');
            Hashtable sqlStringList = new Hashtable();
            for (int i = 0; i < fBillNo.Length; i++)  //
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("update ICMO set FWORKTYPEID = @workTypeID where FBillNo=@FBillNo");
                SqlParameter[] paras = { new SqlParameter("@FBillNo", SqlDbType.NVarChar, 20),
                                  new SqlParameter("@workTypeID", SqlDbType.Int, 20),
                                  };
                paras[0].Value = fBillNo[i];
                paras[1].Value = workTypeID; 
                sqlStringList.Add(sb, paras);
            }
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.connStr, sqlStringList);   //批量插入（事务）                
                return true;
            }
            catch
            {
                return false;
            } 

        }

        public static int adjustProjectPlan(string FbillNo, int days, string UpdateBy) {
            string sql=@"update ICMO set FPlanCommitDate= dateadd(day,@days,FPlanCommitDate),FPlanFinishDate= dateadd(day,@days,FPlanFinishDate),
                        FHeadSelfJ0186=@UpdateBy where FBillNo=@FbillNo and FStatus<3;
                        update SHWorkBillEntry set FPlanStartDate=dateadd(day,@days,FPlanStartDate),FPlanEndDate=dateadd(day,@days,FPlanEndDate) 
                        where FStatus<3 and FEntrySelfz0373 = @FbillNo;"; 
             SqlParameter[] parameters = {
			            new SqlParameter("@FbillNo", SqlDbType.NVarChar,50),
                        new SqlParameter("@days", SqlDbType.Int),
                        new SqlParameter("@UpdateBy", SqlDbType.NVarChar,50),
            };
            parameters[0].Value = FbillNo;
            parameters[1].Value = days;
            parameters[2].Value = UpdateBy;
            int x = SqlHelper.ExecuteNonQuery(SqlHelper.connStrK3,CommandType.Text, sql, parameters);
            new BllLog("adjustProjectPlan","调整计划","FbillNo="+FbillNo+";days="+days+";UpdateBy="+UpdateBy, x.ToString()).Add();//日志
            return x; 
        }
      

        /// <summary>
        /// 获取生产任务单-分页数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <param name="order"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="where"></param>
        /// <param name="totalCount"></param>
        /// <returns>返回DataTable</returns>
        //public DataTable GetDataTablePager(string tableName, string columns, string order, int pageSize, int pageIndex, string where, out int totalCount)
        //{//         dasfsafdas20190313
        //    DataTable dt = AchieveCommon.SqlPagerHelper.GetPagerK3("ICMO", "FBillNo,FStatus,FQty,FCommitQty,FPlanCommitDate,FPlanFinishDate,FStartDate,FFinishDate,FType,FWorkShop,FItemID", order, pageSize, pageIndex, where, out totalCount);
        //    dt.Columns.Add(new DataColumn("FModel"));
        //    dt.Columns.Add(new DataColumn("FName"));
        //    for (int i = 0; i < dt.Rows.Count; i++)
        //    {
        //        DataTable dticitemcore = GetFNameByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
        //        dt.Rows[i]["FModel"] = AchieveCommon.JsonHelper.ColumnToJson(dticitemcore, 0);
        //        dt.Rows[i]["FName"] = AchieveCommon.JsonHelper.ColumnToJson(dticitemcore, 1);
        //    }
        //    return dt;
        //}
         /// <summary>
        /// 直接使用sql获取生产任务单的分页查询，用于替代存储过程
        /// </summary>
        /// <param name="startPage"></param>
        /// <param name="endPage"></param>
        /// <param name="where"></param>
        /// <param name="totalCount"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        //public DataTable GetProjectDataTablePager(int startPage, int endPage, string where, out int totalCount, string order = "FPlanCommitDate desc")
        //{ 
        //    StringBuilder sbsql=new StringBuilder();
        //    sbsql.Append("select 1 as hasChild, FBillNo,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,FStartDate,FFinishDate,FType,FWorkShop,FItemID,FName,FModel,FInterID,FHEADSELFJ0182,FHEADSELFJ0183,fbominterid from (");
        //    sbsql.AppendFormat("select row_number() over(order by {0})", order);
        //    sbsql.Append(" as Rownum,FBillNo,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,FStartDate,FFinishDate,FType,FWorkShop,ICMO.FItemID as FItemID,FName,FModel,FInterID,FHEADSELFJ0182,FHEADSELFJ0183,fbominterid ");
        //    sbsql.Append(" from ICMO left join t_ICITEMCORE on ICMO.FItemID=t_ICITEMCORE.FItemID  ");//连接数据表t_ICITEMCORE为实现物料名称查询；
        //    sbsql.AppendFormat(" where {0}", where);
        //    sbsql.AppendFormat(") as T where T.Rownum between  {0}  and  {1}" ,startPage,endPage);
           
        //    DataTable dt = AchieveCommon.SqlHelper.GetDataTable(SqlHelper.connStrK3,CommandType.Text,sbsql.ToString(),null);
        //    StringBuilder sbtotalsql=new StringBuilder();
        //    sbtotalsql.AppendFormat(" select  count(*) from ICMO left join t_ICITEMCORE on ICMO.FItemID=t_ICITEMCORE.FItemID where {0}",where);
        //   DataTable dttotal= AchieveCommon.SqlHelper.GetDataTable(SqlHelper.connStrK3,CommandType.Text,sbtotalsql.ToString(),null);
        //   totalCount = Convert.ToInt32(dttotal.Rows[0][0]);//select @@rowcount 上一查询的数据
        //   dt.Columns.Add(new DataColumn("FStockQty"));//增加库存数量字段
        //   dt.Columns.Add(new DataColumn("ppbomstatus"));//增加是否领料字段
        //   for (int i = 0; i < dt.Rows.Count; i++)
        //   {
        //       //DataTable dticitemcore = GetFQTYByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
        //       //dt.Rows[i]["FStockQty"] = dticitemcore.Rows[0][0].GetType() ==typeof( DBNull) ? 0 : Convert.ToSingle(dticitemcore.Rows[0][0]);
        //       object o=GetFQTYByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
        //       dt.Rows[i]["FStockQty"]=o is DBNull? 0 : Convert.ToInt32(o);
        //       dt.Rows[i]["ppbomstatus"]=GetppbomstatusByFBillNo(dt.Rows[i]["FBillNo"].ToString()); 
        //   }
        //    return dt;
        //}
        ///// <summary>
        /// add0316
        /// 
        /// </summary>
        /// <param name="startPage"></param>
        /// <param name="endPage"></param>
        /// <param name="where"></param>
        /// <param name="totalCount"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        //public DataTable GetProjectDataTablePagerM(int startPage, int endPage, string where,int fbominterid, out int totalCount, string order = "FPlanCommitDate desc")
        //{
        //    StringBuilder sbsql = new StringBuilder();
        //    sbsql.Append("select FBillNo,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,FStartDate,FFinishDate,FType,FWorkShop,FItemID,FName,FModel,FInterID,FHEADSELFJ0182,FHEADSELFJ0183,fbominterid from (");
        //    sbsql.AppendFormat("select row_number() over(order by {0})", order);
        //    sbsql.Append(" as Rownum,FBillNo,FHEADSELFJ0178,FStatus,fmrpclosed,FQty,FCommitQty,fauxstockqty,FPlanCommitDate,FPlanFinishDate,FCheckDate,FStartDate,FFinishDate,FType,FWorkShop,ICMO.FItemID as FItemID,FName,FModel,FInterID,FHEADSELFJ0182,FHEADSELFJ0183,fbominterid ");
        //    sbsql.Append(" from ICMO left join t_ICITEMCORE on ICMO.FItemID=t_ICITEMCORE.FItemID  ");//连接数据表t_ICITEMCORE为实现物料名称查询；
        //    sbsql.AppendFormat(" where {0}", where);
        //    sbsql.AppendFormat(") as T where T.Rownum between  {0}  and  {1}", startPage, endPage);

        //    DataTable dt = AchieveCommon.SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sbsql.ToString(), null);
        //    StringBuilder sbtotalsql = new StringBuilder();
        //    sbtotalsql.AppendFormat(" select  count(*) from ICMO left join t_ICITEMCORE on ICMO.FItemID=t_ICITEMCORE.FItemID where {0}", where);
        //    DataTable dttotal = AchieveCommon.SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sbtotalsql.ToString(), null);
        //    totalCount = Convert.ToInt32(dttotal.Rows[0][0]);//select @@rowcount 上一查询的数据
        //    dt.Columns.Add(new DataColumn("FStockQty"));//增加库存数量字段
        //    dt.Columns.Add(new DataColumn("ppbomstatus"));//增加是否领料字段
        //    dt.Columns.Add(new DataColumn("level"));
        //    for (int i = 0; i < dt.Rows.Count; i++)
        //    {
        //        //DataTable dticitemcore = GetFQTYByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
        //        //dt.Rows[i]["FStockQty"] = dticitemcore.Rows[0][0].GetType() ==typeof( DBNull) ? 0 : Convert.ToSingle(dticitemcore.Rows[0][0]);
        //        object o = GetFQTYByFItemID(Convert.ToInt32(dt.Rows[i]["FItemID"]));
        //        dt.Rows[i]["FStockQty"] = o is DBNull ? 0 : Convert.ToInt32(o);
        //        dt.Rows[i]["ppbomstatus"] = GetppbomstatusByFBillNo(dt.Rows[i]["FBillNo"].ToString());
        //        if (fbominterid.ToString() == dt.Rows[i]["fbominterid"].ToString())
        //        { 
        //            dt.Rows[i]["level"] = new ProjectBLL().GetFBomLevelByFinterid(dt.Rows[i]["FBillNo"].ToString(), Convert.ToInt32(dt.Rows[i]["fbominterid"].ToString()));
        //        }
        //        else{ 
        //            dt.Rows[i]["level"] = new ProjectBLL().GetFBomLevelByFinterid(dt.Rows[i]["FBillNo"].ToString(),Convert.ToInt32( dt.Rows[i]["fbominterid"].ToString()));
        //        }
              
        //    }
        //    return dt;
        //}
        ////public string GetJsonPager(int startPage, int endPage, string where, out int totalCount, string order = "FPlanCommitDate desc")
        //{
        //    DataTable dt =GetProjectDataTablePager( startPage,  endPage,  where, out  totalCount,  order );
        //    return AchieveCommon.JsonHelper.ToJson(dt);
        //}
      // add  20190316
        //public string GetJsonPagerM(int startPage, int endPage, string where,int fbominterid,out int totalCount, string order = "FPlanCommitDate desc")
        //{
        //    DataTable dt = GetProjectDataTablePagerM(startPage, endPage, where,fbominterid,out  totalCount, order);
        //    return AchieveCommon.JsonHelper.ToJson(dt);
        //}
        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">要取的列名（逗号分开）</param>
        /// <param name="order">排序</param>
        /// <param name="pageSize">每页大小</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="where">查询条件</param>
        /// <param name="totalCount">总记录数</param>
        //public string GetJsonPager(string tableName, string columns, string order, int pageSize, int pageIndex, string where, out int totalCount)
        //{
        //    DataTable dt = GetDataTablePager(tableName, columns, order, pageSize, pageIndex, where, out totalCount);
        //    return AchieveCommon.JsonHelper.ToJson(dt);
        // }
        /// <summary>
      
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <param name="order"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="where"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
  
        public string GetJsonFromSqlK3(string sql)
        {
            DataTable dt = AchieveCommon.SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sql);
            return AchieveCommon.JsonHelper.ToJson(dt);
        }
 
        //新增 20190223 访问IE数据库
        public string GetJsonFromSql(string sql)
        {
            DataTable dt = AchieveCommon.SqlHelper.GetDataTable(SqlHelper.connStr, CommandType.Text, sql);
            return AchieveCommon.JsonHelper.ToJson(dt);
        }
        /// <summary>
        /// 根据物料ID查找物料名称、规格型号等信息
        /// </summary>
        /// <param name="FItemID"></param>
        /// <returns></returns>
        public  DataTable GetFNameByFItemID(int FItemID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select FModel,FName from t_icitemcore");
            sb.Append(" where FItemID=@Id");
            return SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sb.ToString(), new SqlParameter("@Id", FItemID));
        }
        /// <summary>
        /// 根据物料ID查询物料库存数量
        /// </summary>
        /// <param name="FItemID"></param>
        /// <returns></returns>
        public object GetFQTYByFItemID(int FItemID)
        {
            string sql = string.Format(" select sum(FQty) from ICINVENTORy where FItemID={0}", FItemID); 
           return SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql);
            //return SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sb.ToString(), new SqlParameter("@Id", FItemID));
        }
        /// <summary>
        /// 根据单号查询是否领料
        /// </summary>
        /// <param name="FItemID"></param>
        /// <returns></returns>
        public int GetppbomstatusByFBillNo(string FBillNo)
        {
            string sql = string.Format(" select a.fstatus from ppbom as a right join icmo as b on a.ficmointerid=b.finterid where b.fbillno='{0}'", FBillNo);
            //string sql = "select Fstatus from PPBOM where FBillNo='" + FBillNo+"'";
            object o=SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql); 
            if (o is DBNull)
	{
		 return 0;
	}else
	{
        return Convert.ToInt32(o);
	}
           
        }

        public DataTable GetFItemIDByFName(string FName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select FItemID from t_icitemcore");
            sb.Append(" where FName like '%"+FName+"%'");
            return SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sb.ToString(), null);
        }
        public int GetFBomidByFBillNO(string FBillNo)
        {

            string sql = string.Format("select fbominterid from ICMO left join t_ICITEMCORE on ICMO.FItemID=t_ICITEMCORE.FItemID  where fbillno='{0}'", FBillNo);
         
            object o = SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql);
            if (o is DBNull)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(o);
            }
        }
    
        public int GetFBomLevelByFinterid(string FBillNo,int FinterID)
        {
            string sql = "with CTE(id,pid,level) as (select finterid,fitemid,1 as level from icbomchild where finterid='" + FinterID + "' union all select a.fitemid,a.finterid ,b.level+1 from icbomchild a inner join cte b on b.id=a.finterid) select max(level)-1 from cte ";
            object o = SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql);
            if (o is DBNull)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(o);
            }
        }
        /// <summary>
        /// 修改项目状态，项目的确认、取消确认、下达、取消下达
        /// </summary>
        /// <param name="projectID"></param>
        /// <param name="status">0：未确认；1：已确认；2：已下达</param>
        /// <returns></returns>
        public static int confirmProjectByID(string projectID, int status) {
            try
            {
                string sql = string.Format("update tbProject set status={0} where ProjectID = '{1}' ", status, projectID);
                return SqlHelper.ExecuteNonQuerySql(SqlHelper.connStr, sql);
            }
            catch (Exception)
            {
                
                throw;
            } 
        }

        /// <summary>
        /// 根据当前日期时间对项目自动进行确认、下达处理
        /// </summary>
        /// <returns></returns>
        public static int autoConfirmProject() {
           
            //            SELECT     a.ProjectID, a.Status,b.PSTime, b.PETime, b.RSTime, b.RETime
            //FROM         tbProject AS a inner JOIN
            //            tbMgrNodeInfo AS b ON a.ProjectID = b.ProjectID
            //WHERE     (a.Status < 1) AND (b.NodeID = 3) AND (b.PETime < GETDATE())
            try
            {
                List<string> sqllist = new List<string>();
                //按合同签订完成日期，自动确认； NodeID=3为合同签订
                string sql1 =
                        @"update tbProject set Status=1 where ProjectID in
                        (SELECT     a.ProjectID
                        FROM         tbProject AS a inner JOIN
                                    tbMgrNodeInfo AS b ON a.ProjectID = b.ProjectID
                        WHERE     (a.Status < 1) AND (b.NodeID = 3) AND (b.PETime < GETDATE()));";
                sqllist.Add(sql1);
                //按制造计划开始日期，自动下达； NodeID=5为生产制造, NodeID=4为项目设计
                string sql2 =
                        @"update tbProject set Status=2 where ProjectID in
                        (SELECT     a.ProjectID
                        FROM         tbProject AS a inner JOIN
                                    tbMgrNodeInfo AS b ON a.ProjectID = b.ProjectID
                        WHERE     (a.Status < 2) AND (b.NodeID = 5) AND (b.PSTime < GETDATE()));";
                sqllist.Add(sql2);
                return SqlHelper.ExecuteNonQuery(SqlHelper.connStr, sqllist);  
            }
            catch (Exception)
            {
                
                throw;
            }
           
        }
    }
}
